﻿Public Class clsIcis
    Inherits clsWebProvider

    Private WithEvents IcisProject As WRDB.Project.clsProject

    Public Overrides ReadOnly Property Description As String
        Get
            Return "Georgia EPD ICIS/NPDES (Environmental Protection Division Integrated Compliance Information System/National Pollutant Discharge Elimination System) database is currently under development and is only available to Georgia EPD personnel (other users will get an error message). It contains historic data from DMRs (Discharge Monitoring Reports) that have been entered into the EPA ICIS/NPDES database. See also http://dmr.epdsoftware.com"
        End Get
    End Property

    Public Overrides ReadOnly Property URL As String
        Get
            Return "http://dmr.epdsoftware.com"
        End Get
    End Property

    Public Overrides Function GetStations(Optional NameFilter As String = "") As Boolean
        Cancelled = False
        Dim dr As Data.Common.DbDataReader = Nothing
        Try
            IcisProject = New WRDB.Project.clsProject("ICIS", True)
            With IcisProject
                .DB = clsDB.GetDataProvider(clsDB.enumDBType.MySQL, IIf(Debugger.IsAttached, "192.168.0.254x", "gswp.wileng.com"), "icis", "icis", "water")
                If Not .DB.TestConnection Then Throw New System.Data.DataException("Unable to connect to database server; you may not have permission to view these data: " & .DB.DataSource)
                dictStations.Clear()
                dictAgencies.Clear()
                SetProgress(0, -1, "Querying database...")
                Dim sql As String = ""
                With AreaOfInterest
                    sql = String.Format("SELECT Station_ID,Station_Name,Station_Type,Latitude,Longitude,FIPS_Code,HUC FROM Stations WHERE Latitude BETWEEN {0} AND {1} AND Longitude BETWEEN {2} AND {3}", .Top, .Bottom, .Left, .Right)
                End With
                dr = .DB.ExecuteReader(sql)
                While dr IsNot Nothing And dr.Read
                    Dim sta As String = TestNull(dr.GetValue(0), "")
                    Dim nam As String = TestNull(dr.GetValue(1), "")
                    Dim typ As String = TestNull(dr.GetValue(2), "")
                    Dim lat As Single = TestNull(dr.GetValue(3), 0)
                    Dim lon As Single = -Math.Abs(TestNull(dr.GetValue(4), 0))
                    Dim fip As String = TestNull(dr.GetValue(5), "")
                    Dim huc As String = TestNull(dr.GetValue(6), "")
                    If sta <> "" And Math.Abs(lat) > 0.5 And Math.Abs(lon) > 0.5 And Math.Abs(lat) < 90 And Math.Abs(lon) < 180 Then
                        If NameFilter = "" OrElse nam.ToUpper.Contains(NameFilter.Trim.ToUpper) Then
                            dictStations.Add(sta, New clsStation(sta, sta, nam, typ, "EPD", "GaEPD", lat, lon, fip, huc))
                        End If
                    End If
                    If Cancelled Then Return False
                End While
                dr.Close()
                dictAgencies.Add("EPD", New clsAgency("EPD", "GaEPD"))
            End With
            Return True
        Catch ex As Exception
            LastException = ex
            Return False
        Finally
            If dr IsNot Nothing Then dr.Close()
        End Try
    End Function

    Public Overrides Function GetParameters(Optional NameFilter As String = "") As Boolean
        Try
            LastException = Nothing
            Cancelled = False
            dictParameters.Clear()
            Dim StationList As String = ""
            For Each s As String In SelectedStations()
                StationList &= IIf(StationList = "", "", ",") & "'" & s & "'"
            Next

            If StationList <> "" Then
                Dim dr As Data.Common.DbDataReader = IcisProject.DB.ExecuteReader("SELECT Dta.PCode, Anal_Name, Units, Count(Dta.Result) AS Count, MIN(Dta.Date_Time) AS Mindate, MAX(Dta.Date_Time) AS MaxDate FROM (SELECT * FROM Master WHERE Station_ID IN ({0})) Dta,PCodes WHERE Dta.PCode=PCodes.PCode GROUP BY Dta.PCode", StationList)
                While dr IsNot Nothing And dr.Read
                    Dim pcd As String = TestNull(dr.GetValue(0), "")
                    Dim nam As String = TestNull(dr.GetValue(1), "")
                    Dim unt As String = TestNull(dr.GetValue(2), "")
                    Dim cnt As String = TestNull(dr.GetValue(3), "")
                    Dim min As String = TestNull(dr.GetValue(4), "")
                    Dim max As String = TestNull(dr.GetValue(5), "")
                    If NameFilter.Trim = "" OrElse nam.ToUpper.Contains(NameFilter.Trim.ToUpper) Then
                        dictParameters.Add(pcd, New clsParameter("", pcd, pcd, nam, unt, cnt, min, max))
                    End If
                    If Cancelled Then Return False
                End While
                dr.Close()
            End If
            Return True
        Catch ex As Exception
            LastException = ex
            Return False
        End Try
    End Function

    Public Overrides Function GetResults() As Boolean
        Try
            LastException = Nothing
            Cancelled = False
            lstData.Clear()

            Dim StationList As String = ""
            For Each s As String In SelectedStations()
                StationList &= IIf(StationList = "", "", ",") & "'" & s & "'"
            Next
            Dim PCodeList As String = ""
            For Each s As String In SelectedParameters()
                PCodeList &= IIf(PCodeList = "", "", ",") & "'" & s & "'"
            Next

            If StationList <> "" AndAlso PCodeList <> "" Then
                Dim dta As New WRDB.Project.clsDataTable(IcisProject, "Master")
                For Each rcd As WRDB.Project.clsDataRecord In dta.SelectRecords(String.Format("Station_ID IN ({0}) AND PCode IN ({1}) AND Date_Time BETWEEN {2} AND {3}", StationList, PCodeList, IcisProject.DB.DateTime(StartDate), IcisProject.DB.DateTime(EndDate)))
                    With rcd
                        lstData.Add(New WRDB.Project.clsDataRecord(.StationID, .DateTime, .PCode, .Result, .CCode, .RCode, .LEWPct, .DepthM, .Agency, .Validated, .TrackID, .LCode, .SCode, .QCode))
                    End With
                    If Cancelled Then Return False
                Next
            End If
            Return True
        Catch ex As Exception
            LastException = ex
            Return False
        End Try
    End Function

    Public Overrides Function GetResultCount(QuickCount As Boolean) As Boolean
        Cancelled = False
        DataCount = 0
        For Each s As String In SelectedParameters()
            DataCount += dictParameters(s).Count
        Next
        Return True
    End Function

End Class
